package com.ecommerce.product.repository;

import com.ecommerce.product.entity.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

/**
 * 商品数据访问层
 * 
 * @author ecommerce-team
 * @version 1.0.0
 */
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    
    /**
     * 根据ID查询上架商品
     */
    @Query("SELECT p FROM Product p WHERE p.id = :id AND p.status = 1 AND p.deleted = 0")
    Optional<Product> findByIdAndStatusActive(@Param("id") Long id);
    
    /**
     * 根据分类ID查询商品列表
     */
    @Query("SELECT p FROM Product p WHERE p.categoryId = :categoryId AND p.status = 1 AND p.deleted = 0 ORDER BY p.sortOrder DESC, p.createTime DESC")
    Page<Product> findByCategoryId(@Param("categoryId") Long categoryId, Pageable pageable);
    
    /**
     * 根据品牌ID查询商品列表
     */
    @Query("SELECT p FROM Product p WHERE p.brandId = :brandId AND p.status = 1 AND p.deleted = 0 ORDER BY p.sortOrder DESC, p.createTime DESC")
    Page<Product> findByBrandId(@Param("brandId") Long brandId, Pageable pageable);
    
    /**
     * 根据价格区间查询商品
     */
    @Query("SELECT p FROM Product p WHERE p.price BETWEEN :minPrice AND :maxPrice AND p.status = 1 AND p.deleted = 0 ORDER BY p.sortOrder DESC, p.createTime DESC")
    Page<Product> findByPriceRange(@Param("minPrice") BigDecimal minPrice, @Param("maxPrice") BigDecimal maxPrice, Pageable pageable);
    
    /**
     * 根据关键词搜索商品
     */
    @Query("SELECT p FROM Product p WHERE (p.title LIKE %:keyword% OR p.subtitle LIKE %:keyword% OR p.description LIKE %:keyword%) AND p.status = 1 AND p.deleted = 0 ORDER BY p.sortOrder DESC, p.createTime DESC")
    Page<Product> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 查询推荐商品
     */
    @Query("SELECT p FROM Product p WHERE p.isRecommend = 1 AND p.status = 1 AND p.deleted = 0 ORDER BY p.sortOrder DESC, p.createTime DESC")
    Page<Product> findRecommendProducts(Pageable pageable);
    
    /**
     * 查询热销商品
     */
    @Query("SELECT p FROM Product p WHERE p.isHot = 1 AND p.status = 1 AND p.deleted = 0 ORDER BY p.sales DESC, p.sortOrder DESC")
    Page<Product> findHotProducts(Pageable pageable);
    
    /**
     * 查询新品商品
     */
    @Query("SELECT p FROM Product p WHERE p.isNew = 1 AND p.status = 1 AND p.deleted = 0 ORDER BY p.createTime DESC")
    Page<Product> findNewProducts(Pageable pageable);
    
    /**
     * 根据销量排序查询商品
     */
    @Query("SELECT p FROM Product p WHERE p.status = 1 AND p.deleted = 0 ORDER BY p.sales DESC, p.sortOrder DESC")
    Page<Product> findProductsOrderBySales(Pageable pageable);
    
    /**
     * 根据价格升序查询商品
     */
    @Query("SELECT p FROM Product p WHERE p.status = 1 AND p.deleted = 0 ORDER BY p.price ASC, p.sortOrder DESC")
    Page<Product> findProductsOrderByPriceAsc(Pageable pageable);
    
    /**
     * 根据价格降序查询商品
     */
    @Query("SELECT p FROM Product p WHERE p.status = 1 AND p.deleted = 0 ORDER BY p.price DESC, p.sortOrder DESC")
    Page<Product> findProductsOrderByPriceDesc(Pageable pageable);
    
    /**
     * 根据创建时间查询商品（最新）
     */
    @Query("SELECT p FROM Product p WHERE p.status = 1 AND p.deleted = 0 ORDER BY p.createTime DESC")
    Page<Product> findProductsOrderByCreateTime(Pageable pageable);
    
    /**
     * 查询库存不足的商品
     */
    @Query("SELECT p FROM Product p WHERE p.stock <= :threshold AND p.status = 1 AND p.deleted = 0")
    List<Product> findLowStockProducts(@Param("threshold") Integer threshold);
    
    /**
     * 批量查询商品
     */
    @Query("SELECT p FROM Product p WHERE p.id IN :ids AND p.status = 1 AND p.deleted = 0")
    List<Product> findByIds(@Param("ids") List<Long> ids);
    
    /**
     * 统计商品总数
     */
    @Query("SELECT COUNT(p) FROM Product p WHERE p.status = 1 AND p.deleted = 0")
    Long countActiveProducts();
    
    /**
     * 统计分类下商品数量
     */
    @Query("SELECT COUNT(p) FROM Product p WHERE p.categoryId = :categoryId AND p.status = 1 AND p.deleted = 0")
    Long countByCategoryId(@Param("categoryId") Long categoryId);
}